package org.ielts.repository;

import java.util.Arrays;
import java.util.List;

import org.apache.commons.lang.ArrayUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.ielts.domain.base.Classroom;
import org.ielts.domain.base.Lesson;
import org.ielts.domain.base.Student;
import org.ielts.domain.report.StudentQuery;
import org.ielts.domain.security.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import com.yifeng.dao.HibernateRepository;
import com.yifeng.page.table.Paginations;

@Repository
public class StudentRepository extends HibernateRepository<Student, String> {

	@Autowired
	SessionFactory sessionFactory;
	private boolean alias;

	public Student findByNo(String no) {
		Session session = sessionFactory.getCurrentSession();

		Student student = (Student) session.createCriteria(Student.class).add(Restrictions.eq("no", no)).uniqueResult();

		return student;
	}

	public Page<Student> findConsultStarred(Pageable pageable, boolean starred) {
		Session session = sessionFactory.getCurrentSession();

		Criteria criteria = session.createCriteria(Student.class).add(Restrictions.eq("consultStarred", starred));

		criteria.setFirstResult(pageable.getOffset());
		criteria.setMaxResults(pageable.getPageSize());
		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		@SuppressWarnings("unchecked")
		List<Student> students = criteria.list();

		criteria.setFirstResult(0);
		criteria.setProjection(Projections.count("id"));

		Object object = criteria.uniqueResult();

		long total = (Long) object;

		return new PageImpl<Student>(students, pageable, total);
	}

	public long countStarredByConsultant(String id) {
		Session session = sessionFactory.getCurrentSession();

		Criteria criteria = session.createCriteria(Student.class);
		Object object = criteria.add(Restrictions.eq("consultStarred", true)).createCriteria("consultor")
				.add(Restrictions.eq("id", id)).setProjection(Projections.count("id")).uniqueResult();

		long total = (Long) object;

		return total;
	}

	public Iterable<Student> findStarredByConsultant(String id) {
		Session session = sessionFactory.getCurrentSession();

		Criteria criteria = session.createCriteria(Student.class);

		@SuppressWarnings("unchecked")
		List<Student> students = criteria.add(Restrictions.eq("consultStarred", true)).createCriteria("consultor")
				.add(Restrictions.eq("id", id)).list();

		return students;
	}

	public Page<Student> findConsultStarred(StudentQuery studentQuery, Pageable pageable, boolean b) {
		Session session = sessionFactory.getCurrentSession();

		Criteria criteria = session.createCriteria(Student.class);

		if (studentQuery.hasConsultant()) {
			criteria.createAlias("consultor", "consultor");
			criteria.add(Restrictions.in("consultor.id", studentQuery.getConsultant()));
		}

		if (studentQuery.hasConsultStarred()) {
			criteria.add(Restrictions.in("consultStarred", ArrayUtils.toObject(studentQuery.getConsultStarred())));
		}

		alias = false;

		if (studentQuery.hasCourse()) {
			createAlias(criteria);
			criteria.createCriteria("signCourses.course").add(Restrictions.in("id", studentQuery.getCourse()));
		}

		if (studentQuery.isOnlyVoid()) {
			criteria.add(Restrictions.sizeEq("courses", 0));
		} else {
			if (studentQuery.hasLearnStatus()) {
				createAlias(criteria);
				criteria.add(Restrictions.in("signCourses.learnStatus", studentQuery.getLearnStatus()));
			}
		}

		if (studentQuery.hasName()) {
			String name = studentQuery.getName();
			criteria.add(Restrictions.or(
					Restrictions.like("name", name, MatchMode.START),
					Restrictions.or(Restrictions.eq("stuId", name),
							Restrictions.or(Restrictions.eq("no", name), Restrictions.eq("pinyin", name)))));
		}

		if (studentQuery.hasTutor()) {
			criteria.createAlias("tutor", "tutor");
			criteria.add(Restrictions.in("tutor.id", studentQuery.getTutor()));
		}

		if (studentQuery.hasSignDate()) {
			createAlias(criteria);
			criteria.add(Restrictions.between("signCourses.signTime", studentQuery.getSignDate().getStartDate(),
					studentQuery.getSignDate().getEndDate()));
		}

		if (studentQuery.hasCourseDate()) {
			createAlias(criteria);
			criteria.add(Restrictions.between("signCourses.beginTime", studentQuery.getCourseDate().getStartDate(),
					studentQuery.getCourseDate().getEndDate()));
		}

		criteria.setFirstResult(pageable.getOffset());
		criteria.setMaxResults(pageable.getPageSize());
		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		@SuppressWarnings("unchecked")
		List<Student> students = criteria.list();

		criteria.setFirstResult(0);
		criteria.setProjection(Projections.count("id"));

		Object object = criteria.uniqueResult();

		long total = (Long) object;

		return new PageImpl<Student>(students, pageable, total);
	}

	private void createAlias(Criteria criteria) {
		if (!alias) {
			criteria.createAlias("courses", "signCourses");
			alias = true;
		}
	}

	public Iterable<Student> findByStuId(String stuId) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		Iterable<Student> students = session.createCriteria(Student.class).add(Restrictions.eq("stuId", stuId)).list();

		return students;
	}

	public Iterable<Student> findByPinyin(String pinyin) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		Iterable<Student> students = session.createCriteria(Student.class).add(Restrictions.eq("pinyin", pinyin))
				.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();

		return students;
	}

	// 查看学员课程使用情况

	public Page<Student> findByCurseRemainTimes(int times, double rate, String[] ccnames, Pageable pageable) {
		String hql = "select distinct s from Student s left join fetch s.courses c where c.times!=0 ";
		String counthql = "select count(s) from Student s  left join  s.courses c where c.times!=0 ";
		if (times != -1) {
			hql += " and c.times-c.usedTimes<=:times";
			counthql += " and c.times-c.usedTimes<=:times";
		}
		if (rate != -1) {
			hql += " and (c.times-c.usedTimes)/c.times<=:rate ";
			counthql += " and (c.times-c.usedTimes)/c.times<=:rate ";
		}
		if (ccnames != null && ccnames.length > 0) {
			hql += " and s.consultor.name in (:ccnames)";
			counthql += " and s.consultor.name in (:ccnames)";
		}

		Session session = sessionFactory.getCurrentSession();
		Query q = session.createQuery(hql);
		Query cq = session.createQuery(counthql);
		q.setFirstResult(pageable.getOffset() - pageable.getPageSize());
		q.setMaxResults(pageable.getOffset());
		if (times != -1) {
			q.setInteger("times", times);
			cq.setInteger("times", times);
		}
		if (rate != -1) {
			q.setDouble("rate", rate);
			cq.setDouble("rate", rate);
		}
		if (ccnames != null && ccnames.length > 0) {
			q.setParameterList("ccnames", ccnames);
			cq.setParameterList("ccnames", ccnames);
		}
		List<Student> students = q.list();
		long total = (Long) cq.uniqueResult();
		return new PageImpl<Student>(students, pageable, total);
	}

	public Page<Student> findByCurseRemainDate(int day, double rate, String[] ccnames, Pageable pageable) {
		String hql = "select distinct s from Student s left join fetch s.courses c where c.term != '0'";
		String counthql = "select count(s) from Student s  left join  s.courses c where c.term != '0'";

		if (day != -1) {
			hql += " and  day(c.endTime- current_timestamp())<:day";
			counthql += " and  day(c.endTime- current_timestamp())<:day";
		}

		if (rate != -1) {
			hql += " and day(c.endTime- current_timestamp())/day(c.endTime- c.beginTime)<:rate";
			counthql += " and day(c.endTime- current_timestamp())/day(c.endTime- c.beginTime)<:rate";
		}

		if (ccnames != null && ccnames.length > 0) {
			hql += " and s.consultor.name in (:ccnames)";
			counthql += " and s.consultor.name in (:ccnames)";
		}
		Session session = sessionFactory.getCurrentSession();
		Query q = session.createQuery(hql);
		Query cq = session.createQuery(counthql);
		q.setFirstResult(pageable.getOffset() - pageable.getPageSize());
		q.setMaxResults(pageable.getOffset());
		if (day != -1) {
			q.setInteger("day", day);
			cq.setInteger("day", day);
		}
		if (rate != -1) {
			q.setDouble("rate", rate);
			cq.setDouble("rate", rate);
		}
		if (ccnames != null && ccnames.length > 0) {
			q.setParameterList("ccnames", ccnames);
			cq.setParameterList("ccnames", ccnames);
		}

		List<Student> students = q.list();
		long total = (Long) cq.uniqueResult();
		return new PageImpl<Student>(students, pageable, total);
	}

	public Iterable<Student> findByMobile(String mobile) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		Iterable<Student> students = session.createCriteria(Student.class).add(Restrictions.eq("mobile", mobile))
				.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();

		return students;
	}

	public List<Student> findStudentByTutorId(String myid, Pageable pageable) {
		Session session = sessionFactory.getCurrentSession();
		return session.createQuery("from Student s where s.tutor.id=:myid").setString("myid", myid)
				.setFirstResult(pageable.getOffset()).setMaxResults(pageable.getOffset() + pageable.getPageSize())
				.list();
	}

	public long countByTutorId(String myid) {
		Session session = sessionFactory.getCurrentSession();
		return (Long) session.createQuery("select count(s.id) from Student s where s.tutor.id=:myid")
				.setString("myid", myid).uniqueResult();
	}

	public List<Student> findStudentByConsultantId(String myid, Pageable pageable) {
		Session session = sessionFactory.getCurrentSession();
		return session.createQuery("from Student s where s.consultor.id=:myid").setFirstResult(pageable.getOffset())
				.setMaxResults(pageable.getOffset() + pageable.getPageSize()).setString("myid", myid).list();
	}

	public long countByConsultantId(String myid) {
		Session session = sessionFactory.getCurrentSession();
		return (Long) session.createQuery("select count(s.id) from Student s where s.consultor.id=:myid")
				.setString("myid", myid).uniqueResult();
	}

	public boolean existsName(String name) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		Criteria criteria = session.createCriteria(Student.class).add(Restrictions.eq("name", name))
				.setProjection(Projections.count("id"));
		long count = (Long) criteria.uniqueResult();

		return count > 0;
	}

	public Student findByNameAndStuId(String name, String stuId) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		Student student = (Student) session.createCriteria(Student.class).add(Restrictions.eq("name", name))
				.add(Restrictions.eq("stuId", stuId)).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
				.uniqueResult();

		return student;
	}
}
